﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Dynamic;
using System.Data.SqlClient;
using System.Configuration;
using Model;
using Common;
namespace DAL
{
    public class UserDAL
    {
        public static int Save(UserInfo info)
        {
            string sql = string.Format("insert into [user]([OpenID],[NickName],[HeadImg],[Mobile],[UserPwd],[Province],[City],[Area],[Sex],[Money],[Role],[State],[ShowPut],[CreateDate],[EndDate]) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},{9},{10},{11},'{12}','{13}','{14}');select @@identity;", info.OpenID, info.NickName, info.HeadImg, info.Mobile, info.UserPwd, info.Province, info.City, info.Area, info.Sex, info.Money, info.Role, info.State, info.ShowPut, info.CreateDate, info.EndDate);
            return DataConvert.ToInt(CommonDAL.ExecuteScalar(sql));
            
        }
        public static int Update(UserInfo info)
        {
            string sql = string.Format("");
            return CommonDAL.ExecuteNoneQuery(sql) ? info.ID : 0;
        }

        public static UserInfo Login(string username, string userpwd)
        {
            SqlParameter[] sp = new SqlParameter[2];
            sp[0] = new SqlParameter("@Mobile", username);
            sp[1] = new SqlParameter("@UserPwd", userpwd);
            DataRow dr = CommonDAL.GetDataRow("user_login", sp);
            return Simple_Data(dr);
        }
        public static UserInfo FindOneByID(int id)
        {
            DataRow dr = CommonDAL.FindOneByID(id, "[user]");
            return Simple_Data(dr);
        }
        public static UserInfo FindByOpenID(string openid)
        {
            DataRow dr = CommonDAL.GetDataRow(string.Format("select * from [user] where OpenID='{0}' order by EndDate desc", openid));
            return Simple_Data(dr);
        }
        public static UserInfo FindByNickName(string nickname)
        {
            DataRow dr = CommonDAL.GetDataRow(string.Format("select * from [user] where NickName='{0}' order by EndDate desc", nickname));
            return Simple_Data(dr);
        }
        public static List<UserInfo> FindByAdminIDs(string adminids)
        {

            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(adminids))
            {
                dt = CommonDAL.GetDataTable(string.Format("select * from [user] where AdminID in ({0})", adminids));
            }
            List<UserInfo> list = new List<UserInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static int GetAuditCount()
        {
            return DataConvert.ToInt(CommonDAL.ExecuteScalar("select count(id) from [user] where State=0"));
        }
        public static UserInfo FindByHeadImg(string province, string city,string nickname, int sex)
        {
            DataRow dr = CommonDAL.GetDataRow(string.Format("select * from [user] where Province='{0}' and City='{1}' and NickName='{2}' and Sex='{3}'", province, city,nickname,sex));
            return Simple_Data(dr);
        }
        public static bool UpdateOpenID(string openid, string province, string city, string nickname, int sex)
        {
            return CommonDAL.ExecuteNoneQuery(string.Format("update [user] set OpenID='{0}' where Province='{1}' and City='{2}' and NickName='{3}' and Sex='{4}'", openid, province, city, nickname, sex));
        }
        public static int FindCountByAdminID(int adminid)
        {
            return DataConvert.ToInt(CommonDAL.ExecuteScalar(string.Format("select count(1) from [user] where AdminID={0} and Role=2", adminid)));
        }
        public static double FindUserMoney()
        {
            return DataConvert.ToDouble(CommonDAL.ExecuteScalar(string.Format("select sum(Money) from [user] where Money>0 and Money<10000000 and ID<>16779 and ID<>101740")))/100;
        }
        public static bool UpdateRole(int id, float money)
        {
            return CommonDAL.ExecuteNoneQuery(string.Format("update [user] set Money=Money+{0},Role=2 where ID={1}", money, id));
        }
        
        public static List<UserInfo> FindListByIDs(string ids)
        {
            ids = ids.Replace(",,,", ",").Replace(",,", ",");
            DataTable dt = CommonDAL.FindListByIDs(ids, "[user]");
            List<UserInfo> list = new List<UserInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<UserInfo>  FindAuditer()
        {
            DataTable dt = CommonDAL.GetDataTable(string.Format("select * from [user] where Power like '%order-h%'"));
            List<UserInfo> list = new List<UserInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<UserInfo> FindSender()
        {
            DataTable dt = CommonDAL.GetDataTable(string.Format("select * from [user] where Power like '%order-f%'"));
            List<UserInfo> list = new List<UserInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<UserInfo> FindMyHigher(int userid)
        {
            DataTable dt = CommonDAL.GetDataTable(string.Format("select * from [user] where charindex('{0}',','+Lower+',')>0",userid));
            List<UserInfo> list = new List<UserInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static bool Charge(int userid, double money)
        {
            return CommonDAL.ExecuteNoneQuery(string.Format("update [user] set Money = Money + ({0}) where ID={1}", money, userid));
        }

        public static List<UserInfo> FindPage(string keyword, int state, string begindate, string enddate, int startindex, int pagesize, string order, string desc, out int count)
        {
            string filter = "";
            if (state != -2)
            {
                filter += " and [user].State=" + state;
            }
            if (!string.IsNullOrEmpty(begindate) && !string.IsNullOrEmpty(enddate))
            {
                filter += " and [user].CreateDate between '" + DataConvert.ToDateTime(begindate).ToString("yyyy-MM-dd 00:00:00") + "' and '" + DataConvert.ToDateTime(enddate).ToString("yyyy-MM-dd 23:59:59") + "'";
            }
            if (B.MyAdmin.Role == 1)
            {
                if (!string.IsNullOrEmpty(keyword))
                {
                    filter += " and ([user].NickName like '%" + keyword + "%' or [user].Mobile like '%" + keyword + "%' or [user].ID = " + DataConvert.ToInt(keyword) + " or [user].Province like '%" + keyword + "%' or [user].City like '%" + keyword + "%' or [user].Area like '%" + keyword + "%')";
                }
            }
            if (B.MyAdmin.Role == 2)
            {
                string adminids= string.Join(",", AdminDAL.FindAllChildByID(B.MyAdmin.ID).Select(x=>x.ID));
                if(string.IsNullOrEmpty(adminids)){
                    adminids="0";
                }
                filter += " and ([user].AdminID=" + B.MyAdmin.ID + " or [user].AdminID in ("+adminids+"))";

                if (!string.IsNullOrEmpty(keyword))
                {
                    filter += " and ([user].NickName like '%" + keyword + "%' or [user].Mobile like '%" + keyword + "%' or [user].ID = " + DataConvert.ToInt(keyword) + " or [user].Province like '%" + keyword + "%' or [user].City like '%" + keyword + "%' or [user].Area like '%" + keyword + "%')";
                }
            }
            DataTable dt = CommonDAL.FindCPage(startindex, pagesize, order, desc, filter, "[user_page]", out count);
            List<UserInfo> list = new List<UserInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(All_DataAndEndDate(row));
            }
            return list;
        }

        public static List<UserInfo> FindAll(int departid, string keyword)
        {
            string filter = "";
            if (departid != 0)
            {
                filter += " and DepartID=" + departid;
            }
            if (!string.IsNullOrEmpty(keyword))
            {
                filter += " and ([user].UserName like '%" + keyword + "%' or [user].RealName like '%" + keyword + "%')";
            }
            DataTable dt = CommonDAL.GetDataTable("select * from [user] where 1=1 " + filter);
            List<UserInfo> list = new List<UserInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        
        private static UserInfo Simple_Data(DataRow dr)
        {
            UserInfo info = new UserInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.OpenID = DataConvert.ToString(dr["OpenID"]);
                info.NickName = DataConvert.ToString(dr["NickName"]);
                info.HeadImg = DataConvert.ToString(dr["HeadImg"]);
                info.Mobile = DataConvert.ToString(dr["Mobile"]);
                info.UserPwd = DataConvert.ToString(dr["UserPwd"]);
                info.Province = DataConvert.ToString(dr["Province"]);
                info.City = DataConvert.ToString(dr["City"]);
                info.Area = DataConvert.ToString(dr["Area"]);
                info.Sex = DataConvert.ToInt(dr["Sex"]);
                info.Money = DataConvert.ToFloat(dr["Money"])/100;
                info.Role = DataConvert.ToInt(dr["Role"]);
                info.State = DataConvert.ToInt(dr["State"]);
                info.AdminID = DataConvert.ToInt(dr["AdminID"]);
                info.ShowPut = DataConvert.ToInt(dr["ShowPut"]);
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
                info.EndDate = DataConvert.ToDateTime(dr["EndDate"]);//
                info.RoleName = Commons.RoleName[info.Role];
                info.Keyword = DataConvert.ToString(dr["Keyword"]);
                info.Business = DataConvert.ToString(dr["Business"]);
            }
            return info;
        }
        private static UserInfo All_Data(DataRow dr)
        {
            UserInfo info = new UserInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.OpenID = DataConvert.ToString(dr["OpenID"]);
                info.NickName = DataConvert.ToString(dr["NickName"]);
                info.HeadImg = DataConvert.ToString(dr["HeadImg"]);
                info.Mobile = DataConvert.ToString(dr["Mobile"]);
                info.UserPwd = DataConvert.ToString(dr["UserPwd"]);
                info.Province = DataConvert.ToString(dr["Province"]);
                info.City = DataConvert.ToString(dr["City"]);
                info.Area = DataConvert.ToString(dr["Area"]);
                info.Sex = DataConvert.ToInt(dr["Sex"]);
                info.Money = DataConvert.ToFloat(dr["Money"]) / 100;
                info.Role = DataConvert.ToInt(dr["Role"]);
                info.State = DataConvert.ToInt(dr["State"]);
                info.AdminID = DataConvert.ToInt(dr["AdminID"]);
                info.ShowPut = DataConvert.ToInt(dr["ShowPut"]);
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
                info.EndDate = DataConvert.ToDateTime(dr["EndDate"]);//
                info.Keyword = DataConvert.ToString(dr["Keyword"]);
                info.Business = DataConvert.ToString(dr["Business"]);
                info.RoleName = Commons.RoleName[info.Role];
                info.AdminName = DataConvert.ToString(dr["AdminName"]);
            }
            return info;
        }

        //时间转换
        private static UserInfo All_DataAndEndDate(DataRow dr)
        {
            UserInfo info = new UserInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.OpenID = DataConvert.ToString(dr["OpenID"]);
                info.NickName = DataConvert.ToString(dr["NickName"]);
                info.HeadImg = DataConvert.ToString(dr["HeadImg"]);
                info.Mobile = DataConvert.ToString(dr["Mobile"]);
                info.UserPwd = DataConvert.ToString(dr["UserPwd"]);
                info.Province = DataConvert.ToString(dr["Province"]);
                info.City = DataConvert.ToString(dr["City"]);
                info.Area = DataConvert.ToString(dr["Area"]);
                info.Sex = DataConvert.ToInt(dr["Sex"]);
                info.Money = DataConvert.ToFloat(dr["Money"]) / 100;
                info.Role = DataConvert.ToInt(dr["Role"]);
                info.State = DataConvert.ToInt(dr["State"]);
                info.AdminID = DataConvert.ToInt(dr["AdminID"]);
                info.ShowPut = DataConvert.ToInt(dr["ShowPut"]);
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
                info.EndDate = DataConvert.ToDateTime(dr["EndDate"]);//

                info.RoleName = Commons.RoleName[info.Role];
                info.AdminName = DataConvert.ToString(dr["AdminName"]);
                info.StateText = Commons.StateText[info.State];
                info.Keyword = DataConvert.ToString(dr["Keyword"]);
                info.Business = DataConvert.ToString(dr["Business"]);
                info.EndDateText = (string.Format("{0:d}", info.EndDate)).Replace('/', '-');
            }
            return info;
        }
        public static int UpdateUser(UserInfo info)
        {
            string sql = string.Format("update [user] set Mobile ='{0}',UserPwd='{1}',EndDate='{2}',State={3} where ID='{4}'",info.Mobile,info.UserPwd,info.EndDate, info.State, info.ID);
            return CommonDAL.ExecuteNoneQuery(sql) ? info.ID : 0;

        }
        public static bool SetSynch(int id, int state, int role, float money, DateTime enddate, int adminid, string keyword, string business, int showput)
        {
            return CommonDAL.ExecuteNoneQuery(string.Format("update [user] set State={0},Role={1},Money={2},EndDate='{3}', AdminID={4},Keyword='{5}',Business='{6}',ShowPut={7} where ID={8}", state, role, money, enddate, adminid, keyword, business, showput, id));
        }
    }
}
